﻿using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace WebShopData
{
    public class Category
    {
        public Category(string categoryName, string status = "Active", int categoryId = 0)
        {
            CategoryId = categoryId;
            CategoryName = categoryName;
            Status = status;
        }

        public int CategoryId { get; set; }
        public string CategoryName { get; set; }
        public string Status { get; set; }

        public override string ToString()
        {
            return string.Format("{0} {1}", CategoryId, CategoryName);
        }
    }

    public static class CategoryDB
    {
        public static int InsertNewCategory(Category category)
        {
            using (SqlConnection connection = GalaxySale2014DB.GetConnection())
            {
                using (SqlCommand command = connection.CreateCommand())
                {
                    var sqlQuery = new StringBuilder("INSERT INTO [dbo].[Categories]([CategoryName],[Status])");
                    sqlQuery.Append(" VALUES(@CategoryName, @Status)");
                    command.CommandText = sqlQuery.ToString();
                    command.Parameters.Add("CategoryName", SqlDbType.VarChar, 100).Value = category.CategoryName;
                    command.Parameters.Add("Status", SqlDbType.VarChar, 50).Value = category.Status;
                    int affectedRows = command.ExecuteNonQuery();
                    return affectedRows;
                }
            }
        }

        public static List<Category> GetAllCategories()
        {
            return GetCategoriesBase("", new List<SqlParameter>());
        }

        public static Category GetCategoryById(int categoryId)
        {
            string whereLine = " WHERE [CategoryId] = @CategoryId";
            List<SqlParameter> parameters = new List<SqlParameter>();
            SqlParameter sqlParameter = new SqlParameter("CategoryId", SqlDbType.Int);
            sqlParameter.Value = categoryId;
            parameters.Add(sqlParameter);
            List<Category> articles = GetCategoriesBase(whereLine, parameters);
            if (articles.Count == 1)
            {
                return articles[0];
            }
            return null;
        }

        public static List<Category> GetCategoriesBase(string whereLine, List<SqlParameter> parameters)
        {
            DataSet dataSetArticles = GetArticlesAsDataSet(whereLine, parameters);
            var articles = new List<Category>();
            foreach (DataRow row in dataSetArticles.Tables[0].Rows)
            {
                articles.Add(new Category(
                    categoryName: row["CategoryName"].ToString().Trim(),
                    status: row["Status"].ToString().Trim(),
                    categoryId: int.Parse(row["CategoryId"].ToString().Trim())));
            }
            return articles;
        }

        public static DataSet GetArticlesAsDataSet(string whereLine, List<SqlParameter> parameters)
        {
            using (SqlConnection connection = GalaxySale2014DB.GetConnection())
            {
                using (SqlCommand command = connection.CreateCommand())
                {
                    var sqlQuery = new StringBuilder("SELECT [CategoryId],[CategoryName],[Status]");
                    sqlQuery.Append(" FROM [dbo].[CategoriesNotDeletedView]");
                    sqlQuery.Append(whereLine);
                    command.CommandText = sqlQuery.ToString();
                    foreach (SqlParameter sqlParameter in parameters)
                    {
                        command.Parameters.Add(sqlParameter);
                    }
                    DataSet result = GalaxySale2014DB.GetDataSet(command);
                    return result;
                }
            }
        }

        public static int UpdateCategory(Category category)
        {
            using (SqlConnection connection = GalaxySale2014DB.GetConnection())
            {
                using (SqlCommand command = connection.CreateCommand())
                {
                    var sqlQuery = new StringBuilder("UPDATE [dbo].[Categories]");
                    sqlQuery.Append(" SET [CategoryName] = @CategoryName");
                    sqlQuery.Append(", [Status] = @Status");
                    sqlQuery.Append(" WHERE [CategoryId] = @CategoryId");
                    command.CommandText = sqlQuery.ToString();

                    command.Parameters.Add("CategoryName", SqlDbType.VarChar, 100).Value = category.CategoryName;
                    command.Parameters.Add("Status", SqlDbType.VarChar, 50).Value = category.Status;
                    command.Parameters.Add("CategoryId", SqlDbType.Int).Value = category.CategoryId;

                    int affectedRows = command.ExecuteNonQuery();
                    return affectedRows;
                }
            }
        }

        public static int DeleteCategoryById(int categoryId)
        {
            Category category = GetCategoryById(categoryId);
            category.Status = "Deleted";
            return UpdateCategory(category);
            //using (SqlConnection connection = GalaxySale2014DB.GetConnection())
            //{
            //    using (SqlCommand command = connection.CreateCommand())
            //    {
            //        var sqlQuery = new StringBuilder("DELETE FROM [dbo].[Categories]");
            //        sqlQuery.Append(" WHERE [CategoryId] = @CategoryId ");
            //        command.CommandText = sqlQuery.ToString();

            //        command.Parameters.Add("CategoryId", SqlDbType.Int).Value = categoryId;

            //        int affectedRows = command.ExecuteNonQuery();
            //        return affectedRows;
            //    }
            //}
        }
    }
}